﻿/*
#------------------------------------------------------------------------------
#-- Program Name:	[dbo].[tblServer]
#-- Purpose:		Stores a list of all servers discovered on the network.
#--					When a server is discovered it will insert as active = 0
#--					and disabled = 0.  These records need to be reviewed and
#--					configured by a person
#--	Last Update:	06/05/2012
#--					For a complete history - please review comments in Version
#--					Control.
#------------------------------------------------------------------------------
*/
CREATE TABLE [dbo].[tblServer]
(
	[srv_id]						INT								IDENTITY (1, 1) NOT NULL,
	[srv_last_update]				DATETIME						NOT NULL,
	[srv_last_error]				DATETIME						NULL,
	[srv_date_discovered]			DATETIME						NULL,
	[srv_is_disabled]				BIT								NOT NULL,
	[srv_is_active]					BIT								NOT NULL,
	[srv_description]				VARCHAR(1000)					NULL,
	[srv_server_name]				VARCHAR(60)						NULL,
	[srv_engine_edition]			INT								NULL,
	[srv_edition_id]				INT								NULL,
	[srv_product_version]			VARCHAR(25)						NULL,
	[srv_product_level]				VARCHAR(5)						NULL,
	[srv_has_master_jobs]			BIT								NULL,
	[srv_has_local_jobs]			BIT								NULL,
	[srv_backup_directory]			VARCHAR(255)					NULL,
	[srv_is_mixed_mode]				BIT								NULL,
	[srv_sql_service]				VARCHAR(255)					NULL,
	[srv_agent_service]				VARCHAR(255)					NULL,
	[srv_type_ro]					AS (CASE WHEN UPPER(LEFT([srv_server_name], 2)) = 'SQ' 
												  OR CHARINDEX('QA', [srv_server_name]) > 0 
											 THEN 'QA' 
											 WHEN UPPER(LEFT([srv_server_name], 2)) ='SD' 
												  OR UPPER(LEFT([srv_server_name], 2)) = 'ST' 
												  OR CHARINDEX('TST',[srv_server_name])>(0) 
												  OR CHARINDEX('TRN', [srv_server_name]) > 0 
												  OR CHARINDEX('DEV',[srv_server_name])>(0) 
												  OR CHARINDEX('TQT', [srv_server_name]) > 0 
												  OR [srv_server_name] LIKE '(local)%'
											 THEN 'Test' 
											 ELSE 'Production' 
										END),
	[srv_edition_ro]				AS (CASE [srv_edition_id] 
											WHEN -1253826760 THEN 'Desktop'
											WHEN -1592396055 THEN 'Express'
											WHEN -1534726760 THEN 'Standard'
											WHEN 1333529388 THEN 'Workgroup'
											WHEN 1804890536 THEN 'Enterprise'
											WHEN -323382091 THEN 'Personal'
											WHEN -2117995310 THEN 'Developer'
											WHEN 610778273 THEN 'Enterprise Evaluation'
											WHEN 1044790755 THEN 'Windows Embedded SQL'
											WHEN 4161255391 THEN 'Express with Advanced Services'
											ELSE 'Unknown'
										END),
	[srv_version_major_ro]			AS (CONVERT(int, 
									   (CASE WHEN PARSENAME(CONVERT(varchar, [srv_product_version], 0), 4) IS NULL 
												  THEN PARSENAME(CONVERT(varchar, [srv_product_version], 0), 3) 
											 ELSE PARSENAME(CONVERT(varchar, [srv_product_version], 0), 4) 
									   END), 0)),
	[srv_version_minor_ro]			AS (CONVERT(int, 
									   (CASE WHEN PARSENAME(CONVERT(varchar,[srv_product_version], 0), 4) IS NULL 
											 THEN PARSENAME(CONVERT(varchar,[srv_product_version], 0), 2) 
											 ELSE PARSENAME(CONVERT(varchar,[srv_product_version], 0),(3)) 
										END), 0)),
	[srv_backup_server_ro]			AS (UPPER(CASE WHEN LEFT([srv_backup_directory], 2)='\\' 
											 THEN substring([srv_backup_directory], 3, CHARINDEX('\',[srv_backup_directory], 3) - 3)
										END)),
	[srv_is_vendor_managed_ro]		AS ([dbo].[fncIsVendorManaged]([srv_server_name])),
	CONSTRAINT						[PK_tblServer]					PRIMARY KEY NONCLUSTERED ([srv_id] ASC)
);
